package ca.llsutherland.squash.dao;

import java.sql.ResultSet;
import java.sql.SQLException;

import ca.llsutherland.squash.domain.Administrator;
import ca.llsutherland.squash.domain.Clock;
import ca.llsutherland.squash.domain.Greeting;
import ca.llsutherland.squash.domain.helper.GreetingsHelper;
import ca.llsutherland.squash.exceptions.ValidationException;
import ca.llsutherland.squash.utils.StringUtils;

public class GreetingsDao extends BaseDao {

    private static GreetingsDao instance = new GreetingsDao();

    @Override
    public String getUnderlyingTableName() {
        return "GREETINGS";
    }

    public static GreetingsDao getInstance() {
        return instance ;
    }

    public Greeting create(Greeting item) {
        String sql = "INSERT INTO " + getUnderlyingTableName() + " (GREETING, DATE_CREATED, DATE_UPDATED, DISPLAY_DATE, REMOVE_DATE, ADMINISTRATOR_ID) VALUES (" 
            + StringUtils.addSingleQuotes(item.getMessage()) + ", "
            + StringUtils.addSingleQuotes(item.getDateCreated().toString()) + ", "
            + StringUtils.addSingleQuotes(item.getDateUpdated().toString()) + ", "
            + StringUtils.addSingleQuotes(item.getDisplayDate().toString()) + ", "
            + StringUtils.addSingleQuotes(item.getRemoveDate().toString()) + ", "
            + item.getCreatedBy().getId() + ")";
        Long lastInsertedId = super.create(sql);
        return findById(lastInsertedId);
    }

    private Greeting findById(Long id) {
        String sql = getFindByIdQuery(id);
        return buildGreeting(sql);
    }

    private Greeting buildGreeting(String sql) {
        Greeting item = null;
        ResultSet rs = super.findByQuery(sql);
        
        try {
            if(rs.next()){
                String message = rs.getString("GREETING");
                Clock dateCreated = new Clock(rs.getTimestamp("DATE_CREATED").getTime());
                Clock dateUpdated = new Clock(rs.getTimestamp("DATE_UPDATED").getTime());
                Clock displayDate = new Clock(rs.getTimestamp("DISPLAY_DATE").getTime());
                Clock removeDate = new Clock(rs.getTimestamp("REMOVE_DATE").getTime());
                Administrator administrator = AdministratorsDao.getInstance().findById(rs.getLong("ADMINISTRATOR_ID"));
                Long id = Long.valueOf(rs.getLong("ID"));
                item = GreetingsHelper.createNewGreeting(message, dateCreated, dateUpdated, displayDate, removeDate, administrator, id);
            }
            rs.close();
        } catch (SQLException e) {
            throw new ValidationException(e.getMessage() + " in buildGreeting()");
        } finally {
            rs = null;            
        }
        
        return item;
    }

}
